package com.hxt.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.collect.Lists;
import com.hxt.framework.utils.JsonUtil;
import com.hxt.xt.service.z.bean.ProdcutBean;

/**
 * excel读写工具类
 */
@SuppressWarnings("deprecation")
public class POIUtil {
	private final static String xls = "xls";
	private final static String xlsx = "xlsx";

	/**
	 * 读入excel文件，解析后返回
	 * 
	 * @param file
	 * @throws IOException
	 */
	public static List<String[]> readExcelSrc(String key) throws Exception {
		// 获得Workbook工作薄对象
//		Workbook workbook = getWorkBookByMinio(key);
		InputStream in = null;

		// 创建Workbook工作薄对象，表示整个excel
		Workbook workbook = null;

		// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
		if (key.endsWith(xls)) {
			// 2003
//					workbook = new HSSFWorkbook(is,true);

		} else if (key.endsWith(xlsx)) {
			// 2007
			in = MinioClientUtils.getObjectToInputStream(key);
			workbook = new XSSFWorkbook(in);

		}

		// 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
		List<String[]> list = new ArrayList<String[]>();
		if (workbook != null) {

			for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
				// 获得当前sheet工作表
				Sheet sheet = workbook.getSheetAt(sheetNum);
				if (sheet == null) {
					continue;
				}
				Row row0 = sheet.getRow(0);
				// 取列第一行的字段长度
				int lastCellNum = row0.getPhysicalNumberOfCells();
				// 获得当前行的开始列
				int firstCellNum = 0;
				// 获得当前sheet的开始行
				int firstRowNum = sheet.getFirstRowNum();
				// 获得当前sheet的结束行
				int lastRowNum = sheet.getLastRowNum();
				// 循环除了第一行的所有行
				for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
					// 获得当前行
					Row row = sheet.getRow(rowNum);
					if (row == null) {
						continue;
					}
//					
					String[] cells = new String[lastCellNum];
					// 循环当前行
					for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
						Cell cell = row.getCell(cellNum);
						cells[cellNum] = getCellValue(cell);
					}
					list.add(cells);
				}
			}
			workbook.close();
			in.close();
		}
		return list;
	}

	/**
	 * 读入excel文件，解析后返回
	 * 
	 * @param file
	 * @throws IOException
	 */
	public static List<String[]> readExcel(String key, String content) throws Exception {
		// 获取模板内容索引
		List<Integer> index = Lists.newArrayList();

		List<String> conList = JsonUtil.deserializeRequstVo(content, List.class);
		for (int x = 0; x < conList.size(); x++) {
			String con = conList.get(x);
			if (con.contains(":")) {
				String[] ss = con.split(":");
				index.add(ExcelTool.excelColStrToNum(ss[0], ss[0].length()));

			} else {
				index.add(-1);
			}

		}
		System.out.println(conList);

		// 获得Workbook工作薄对象
//		Workbook workbook = getWorkBookByMinio(key);
		InputStream in = null;

		// 创建Workbook工作薄对象，表示整个excel
		Workbook workbook = null;

		// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
		if (key.endsWith(xls)) {
			// 2003
//					workbook = new HSSFWorkbook(is,true);

		} else if (key.endsWith(xlsx)) {
			// 2007
			in = MinioClientUtils.getObjectToInputStream(key);
			workbook = new XSSFWorkbook(in);

		}

		// 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
		List<String[]> list = new ArrayList<String[]>();
		if (workbook != null) {

			for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
				// 获得当前sheet工作表
				Sheet sheet = workbook.getSheetAt(sheetNum);
				if (sheet == null) {
					continue;
				}
				Row row0 = sheet.getRow(0);
				// 取列第一行的字段长度
				int lastCellNum = row0.getPhysicalNumberOfCells();
				// 获得当前行的开始列
				int firstCellNum = 0;
				// 获得当前sheet的开始行
				int firstRowNum = sheet.getFirstRowNum();
				// 获得当前sheet的结束行
				int lastRowNum = sheet.getLastRowNum();
				// 循环除了第一行的所有行
				for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
					// 获得当前行
					Row row = sheet.getRow(rowNum);
					if (row == null) {
						continue;
					}
//					
//					String[] cells = new String[lastCellNum];
					String[] cells = new String[14];
					// 循环当前行

					for (int cellNum = 0; cellNum < 14; cellNum++) {

						if (index.get(cellNum) == -1) {
							cells[cellNum] = "";
						} else {
							Cell cell = row.getCell(index.get(cellNum) - 1);
							cells[cellNum] = getCellValue(cell);
						}

					}
					list.add(cells);
				}
			}
			workbook.close();
			in.close();
		}
		return list;
	}

	public static String getCellValue(Cell cell) {
		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}

		String result = new String();
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
				SimpleDateFormat sdf = null;
				if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
					sdf = new SimpleDateFormat("HH:mm");
				} else {// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				}
				Date date = cell.getDateCellValue();
				result = sdf.format(date);
			} else if (cell.getCellStyle().getDataFormat() == 14 || cell.getCellStyle().getDataFormat() == 31
					|| cell.getCellStyle().getDataFormat() == 57 || cell.getCellStyle().getDataFormat() == 58
					|| cell.getCellStyle().getDataFormat() == 20 || cell.getCellStyle().getDataFormat() == 32) {

				short format = cell.getCellStyle().getDataFormat();
				SimpleDateFormat sdf = null;
				if (format == 14 || format == 31 || format == 57 || format == 58) {
					// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				} else if (format == 20 || format == 32) {
					// 时间
					sdf = new SimpleDateFormat("HH:mm");
				}
				double value = cell.getNumericCellValue();
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				result = sdf.format(date);

			} else {
				double value = cell.getNumericCellValue();
				CellStyle style = cell.getCellStyle();
				DecimalFormat format = new DecimalFormat();
				String temp = style.getDataFormatString();
				// 单元格设置成常规
				if (temp.equals("General")) {
					format.applyPattern("#");
				}
				result = format.format(value);
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:// String类型
			result = cell.getRichStringCellValue().toString();
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			result = "";
		default:
			result = "";
			break;
		}
		return result;
	}

	public static void main(String[] args) throws Exception {
	}
}